﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using hedefgrup.reports.renault.database;

namespace hedefgrup.reports.common
{
    public class Report_Make_Model : Report_Abstract
    {
        internal class Storage
        {
            internal string Make;
            internal string Model;
            internal float S18_1;
            internal float S17_1;
            internal float S2_1;
            internal float S4_1;
            internal float S41_1;
            internal float S4_1_S41_1;
            internal float S6_1;
            internal float S7_1;
            internal float S7a_1;
            internal float S8_Avg;
            internal float S8b_1;
            internal float Count;
        }

        protected override void SetCriteriaList()
        {
            Criterias.Add(new Criteria_Roll() { Context = Context });
            Criterias.Add(new Criteria_EndMonth() { Context = Context });
            Criterias.Add(new Criteria_Network() { Context = Context });
            Criterias.Add(new Criteria_Organization() { Context = Context });
        }

        private void Apply(int count, int s18_1, int s17_1, int s2_1, int s4_1, int s41_1, int s4_1_S41_1, int s6_1, int s7_1, int S7a_1, int s8_Avg, int s8b_1, params Storage[] storages)
        {
            foreach (Storage storage in storages)
            {
                if (storage != null)
                {
                    storage.Count += count;
                    storage.S17_1 += s17_1;
                    storage.S18_1 += s18_1;
                    storage.S2_1 += s2_1;
                    storage.S4_1 += s4_1;
                    storage.S41_1 += s41_1;
                    storage.S4_1_S41_1 += s4_1_S41_1;
                    storage.S6_1 += s6_1;
                    storage.S7_1 += s7_1;
                    storage.S7a_1 += S7a_1;
                    storage.S8_Avg += s8_Avg;
                    storage.S8b_1 += s8b_1;
                }
            }
        }

        private void AppendResult(ReportResult results, Storage storage, bool bold)
        {
            Row dealerRow = new Row();
            results.Rows.Add(dealerRow);
            dealerRow.Cells.Add(new Cell() { Text = storage.Make, Bold = bold });
            dealerRow.Cells.Add(new Cell() { Text = storage.Model, Bold = bold });
            dealerRow.Cells.Add(new Cell() { Text = storage.Count.ToString(), Bold = bold });
            float s18Val = 100 * (storage.S18_1 / storage.Count);
            string s18Color = s18Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s17Val = 100 * (storage.S17_1 / storage.Count);
            string s17Color = s17Val > Global.TRESHOLD_CALL_DEALER ? "Red" : "Green";
            float s2Val = 100 * (storage.S2_1 / storage.Count);
            string s2Color = s2Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s4Val = 100 * (storage.S4_1 / storage.Count);
            string s4Color = s4Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s41Val = 100 * (storage.S41_1 / storage.Count);
            string s41Color = s41Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s4_1_s41_1Val = 100 * (storage.S4_1_S41_1 / storage.Count);
            string s4_1_s41_1Color = s4_1_s41_1Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s6Val = 100 * (storage.S6_1 / storage.Count);
            string s6Color = s6Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s7Val = 100 * (storage.S7_1 / storage.Count);
            string s7Color = s7Val < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s7aVal = 100 * (storage.S7a_1 / storage.Count);
            string s7aColor = s7aVal < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s8Avg = (storage.S8_Avg / storage.Count);
            string s8Color = 10 * s8Avg < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";
            float s8bVal = 100 * (storage.S8b_1 / storage.Count);
            string s8bColor = s8bVal < Global.TRESHOLD_RECOMMEND_RENAULT ? "Red" : "Green";

            if (Context.CompatibleProjectName == Global.PROJECT_QVN2013)
            {
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s18Val), Color = s18Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s17Val), Color = s17Color, Bold = bold });
            }
            else
            {
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s2Val), Color = s2Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s41Val), Color = s41Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s4Val), Color = s4Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s4_1_s41_1Val), Color = s4_1_s41_1Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s6Val), Color = s6Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s7Val), Color = s7Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.0}", s7aVal), Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.00}", s8Avg), Color = s8Color, Bold = bold });
                dealerRow.Cells.Add(new Cell() { Text = string.Format("{0:0.00}", s8bVal), Color = s8bColor, Bold = bold });
            }
        }

        public override ReportResult GenerateReport()
        {
            DataTable rawData = db.query
            (
                "SELECT AY, YIL, MARKA, MODELGRUP, MODEL, CNT = COUNT(*), " +
                (
                    Context.CompatibleProjectName == Global.PROJECT_QVN2013 ? "S18, S17 " : "S2, S4, S4_1, S4_1_S41_1 = CASE WHEN S4 = 1 OR S4_1 = 1 THEN 1 ELSE 2 END, S6, S7, S7A, S8, S8b "
                ) +
                "FROM " + Context.TableName + " " +
                GetSqlStatementFilter() +
                " GROUP BY AY, YIL, MARKA, MODELGRUP, MODEL, " + (Context.CompatibleProjectName == Global.PROJECT_QVN2013 ? "S18, S17 " : "S2, S4, S4_1, CASE WHEN S4 = 1 OR S4_1 = 1 THEN 1 ELSE 2 END, S6, S7, S7A, S8, S8b ") +
                " ORDER BY MARKA DESC, MODELGRUP ASC, MODEL ASC",
                base.GetSqlStatementParameters()
            );

            ReportResult results = new ReportResult(this);
            Row headerRow = new Row();
            results.HeaderRows.Add(headerRow);
            headerRow.Cells.Add(new Cell() { Text = "Marka" });
            headerRow.Cells.Add(new Cell() { Text = "Model" });
            headerRow.Cells.Add(new Cell() { Text = "Baz" });
            if (Context.CompatibleProjectName == Global.PROJECT_QVN2013)
            {
                headerRow.Cells.Add(new Cell() { Text = "S18 - Tavsiye" });
                headerRow.Cells.Add(new Cell() { Text = "S17 - Geri Dönüş Oranı" });
            }
            else
            {
                headerRow.Cells.Add(new Cell() { Text = "S2 - " + Context.Questionnaire.GetChild("S2").Text });
                headerRow.Cells.Add(new Cell() { Text = "S4_1 - " + Context.Questionnaire.GetChild("S4_1").Text });
                headerRow.Cells.Add(new Cell() { Text = "S4 - " + Context.Questionnaire.GetChild("S4").Text });
                headerRow.Cells.Add(new Cell() { Text = "S4_1_S41_1 - " + Context.Questionnaire.GetChild("S4_1_S41_1").Text });
                headerRow.Cells.Add(new Cell() { Text = "S6 - " + Context.Questionnaire.GetChild("S6").Text });
                headerRow.Cells.Add(new Cell() { Text = "S7 - " + Context.Questionnaire.GetChild("S7").Text });
                headerRow.Cells.Add(new Cell() { Text = "S7a - " + Context.Questionnaire.GetChild("S7A").Text });
                headerRow.Cells.Add(new Cell() { Text = "S8 - " + Context.Questionnaire.GetChild("S8").Text + "<br/>10 Üzerinden Ortalama" });
                headerRow.Cells.Add(new Cell() { Text = "S8b - " + Context.Questionnaire.GetChild("S8b").Text });
            }

            SortedList<string, Storage> storage = new SortedList<string, Storage>();
            List<Storage> makes = new List<Storage>();
            SortedList<string, List<Storage>> makeModelGroups = new SortedList<string, List<Storage>>();
            SortedList<string, List<Storage>> modelGroupModels = new SortedList<string, List<Storage>>();

            Storage totalStorage = new Storage() { Make = "Genel" };

            foreach (DataRow reportRow in rawData.Rows)
            {
                string make = reportRow["MARKA"].ToString();
                string modelGroup = reportRow["MODELGRUP"].ToString();
                string model = reportRow["MODEL"].ToString();

                int count = Convert.ToInt32(reportRow["CNT"]);

                int s18_1 = Context.CompatibleProjectName == Global.PROJECT_QVN2013 ? Convert.ToInt32(reportRow["S18"]) == 1 ? count : 0 : 0;
                int s17_1 = Context.CompatibleProjectName == Global.PROJECT_QVN2013 ? Convert.ToInt32(reportRow["S17"]) == 1 ? count : 0 : 0;
                int s2_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? Convert.ToInt32(reportRow["S2"]) == 1 ? count : 0 : 0;
                int s4_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? reportRow["S4"] != DBNull.Value ? Convert.ToInt32(reportRow["S4"]) == 1 ? count : 0 : 0 : 0;
                int s41_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? reportRow["S4_1"] != DBNull.Value ? Convert.ToInt32(reportRow["S4_1"]) == 1 ? count : 0 : 0 : 0;
                int s4_1_s41_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? reportRow["S4_1_S41_1"] != DBNull.Value ? Convert.ToInt32(reportRow["S4_1_S41_1"]) == 1 ? count : 0 : 0 : 0;
                int s6_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? Convert.ToInt32(reportRow["S6"]) == 1 ? count : 0 : 0;
                int s7_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? Convert.ToInt32(reportRow["S7"]) == 1 ? count : 0 : 0;
                int S7a_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? reportRow["S7A"] != DBNull.Value ? Convert.ToInt32(reportRow["S7A"]) == 1 ? count : 0 : 0 : 0;
                int s8_Avg = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? reportRow["S8"] != DBNull.Value ? Convert.ToInt32(reportRow["S8"]) * count : 0 : 0;
                int s8b_1 = Context.CompatibleProjectName == Global.PROJECT_SATIS_SUREC ? reportRow["S8b"] != DBNull.Value ? Convert.ToInt32(reportRow["S8b"]) == 1 ? count : 0 : 0 : 0;

                if (!string.IsNullOrEmpty(make) && !storage.ContainsKey(make)) storage.Add(make, new Storage() { Make = make });
                if (!string.IsNullOrEmpty(make) && !string.IsNullOrEmpty(modelGroup) && !storage.ContainsKey(modelGroup)) storage.Add(modelGroup, new Storage() { Make = make, Model = modelGroup });
                if (!string.IsNullOrEmpty(make) && !string.IsNullOrEmpty(model) && !storage.ContainsKey(model)) storage.Add(model, new Storage() { Make = make, Model = model });
                if (!string.IsNullOrEmpty(make) && !makeModelGroups.ContainsKey(make)) makeModelGroups.Add(make, new List<Storage>());
                if (!string.IsNullOrEmpty(modelGroup) && !modelGroupModels.ContainsKey(modelGroup)) modelGroupModels.Add(modelGroup, new List<Storage>());

                Storage makeStorage = !string.IsNullOrEmpty(make) ? storage[make] : null;
                Storage modelGroupStorage = !string.IsNullOrEmpty(modelGroup) ? storage[modelGroup] : null;
                Storage modelStorage = !string.IsNullOrEmpty(model) ? storage[model] : null;

                if (makeStorage != null && !makes.Contains(makeStorage)) makes.Add(makeStorage);

                if (!string.IsNullOrEmpty(make) && modelGroupStorage != null && !makeModelGroups[make].Contains(modelGroupStorage)) makeModelGroups[make].Add(modelGroupStorage);
                if (!string.IsNullOrEmpty(modelGroup) && modelStorage != null && !modelGroupModels[modelGroup].Contains(modelStorage)) modelGroupModels[modelGroup].Add(modelStorage);

                Apply(count, s18_1, s17_1, s2_1, s4_1, s41_1, s4_1_s41_1, s6_1, s7_1, S7a_1, s8_Avg, s8b_1, totalStorage, makeStorage, modelGroupStorage, modelStorage);
            }

            AppendResult(results, totalStorage, true);

            foreach (Storage makeStorage in makes)
            {
                AppendResult(results, makeStorage, true);
                foreach (Storage makeModelGroupStorage in makeModelGroups[makeStorage.Make])
                {
                    AppendResult(results, makeModelGroupStorage, true);
                    foreach (Storage modelStorage in modelGroupModels[makeModelGroupStorage.Model])
                    {
                        AppendResult(results, modelStorage, false);
                    }
                }
            }

            return results;
        }

        public override string DisplayName
        {
            get { return "Marka Model Raporu"; }
        }

        public override bool SupportsProject(string projectName)
        {
            return base.SupportsProject(projectName) || projectName == Global.PROJECT_SATIS_SUREC;
        }

        public override string SortPrefix
        {
            get { return "0x03"; }
        }

        public override bool Authorized
        {
            get
            {
                return base.GetAuthLevel() != "dealer";
            }
        }
    }
}
